package unit;

import model.Speciality;
import model.Vet;
import model.VetSpeciality;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class VetDao {
    public static List<Vet> search(String vetName, String specName) throws Exception {
        List<Vet> vets = new ArrayList<Vet>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/petclinic?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "root", "123456");
            con.setAutoCommit(false);
            ps = con.prepareStatement(
                    "SELECT distinct t_vet*FROM t_vet_speciality"
                            + "INNER JOIN t_speciality ON (t_vet_speciality.specId = t_ver.id)"
                            + "INNER JOIN ph.t_vet ON (t_vet _speciality.vetId)"
                            + "where t_vet.name like ?and t_speciality.name like?"
            );
            ps.setString(1, "%" + vetName + "%");
            ps.setString(2, "%" + specName + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                Vet v = new Vet();
                v.setId(rs.getInt("id"));
                v.setName(rs.getString("name"));
                vets.add(v);
            }
            for (Vet v : vets) {
                rs = ps.executeQuery(
                        "SELECT t_speciality*FROM t_vet_speciality"
                                + "INNER JOIN t_speciality ON (t_vet_speciality.specId = t_speciality.id)"
                                + "INNER JOIN ph.t_vet ON (t_vet_speciality..vetId = t_vet.id)"
                                + "where t_vet.id = " + v.getId()
                );
                while (rs.next()) {
                    Speciality spec = new Speciality();
                    spec.setId(rs.getInt("id"));
                    spec.setName(rs.getString("name"));
                    v.getSpecs().add(spec);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库访问异常：" + e);
        }finally{
            if (rs!=null)
                rs.close();
            if (ps!=null);
            ps.close();
            if (con!=null);
            con.close();
        }
        return vets;
    }
}
